<?php
class Search_Model_Selchizutodofuken extends Apm_Model_Base{

	public function getChizuGazoData($params)
	{
		$db_params = array();
		$prefCd = null;

		// 北海道の場合、特殊処理
		if($params['PrefCd'][0] == '01'){
			$prefCd = "90";
		}else{
			$prefCd = $params['PrefCd'][0];
		}


		// 表示地図画像名取得
		$sql = $this->generateChizuGazoSql($prefCd);

		return $this->dbGetData($sql,$db_params);
	}

	public function getSikucyosonData($params)
	{
		$db_params = array();
		$prefCd = null;

		$sql = null;
		$todouhuken = $params['PrefCd'][0];
		// 北海道の場合、特殊処理
		if($todouhuken == '01'){
			$sql = $this->generateHokkaidoSql($params);
		}elseif($todouhuken == '50' || $todouhuken == '51' || $todouhuken == '52' || $todouhuken == '53'){
			$sql = $this->generateSikucyosonSql($todouhuken,"01");
		}else{
			// 表示地図画像名取得
			$sql = $this->generateSikucyosonSql($todouhuken,$todouhuken);
		}

		return $this->dbGetList($sql,$db_params);
	}

	public function getTodofukenChizuData($params)
	{
		$db_params = array();
		$option = null;

		// 北海道の場合は、処理が異なる
		if($params['PrefCd'][0] == "01"){
			$option = " ORDER BY M84_SHIKUCHOUSON_CD";
		}else{
			$option = " AND M84_IDO IS NULL";
		}

		$sql = $this->generateTodofukenChizuSql($params['PrefCd'][0],$option);

		return $this->dbGetList($sql,$db_params);
	}


   /**
	* 市区町村一覧データを表示用に変換
	*
	* @access	public
	* @param	array ※参照渡し
	* @return	void
	*/
	public function convertData($params,&$data)
	{

		$data['Mapping'] = array();

		// 北海道の場合、特殊処理
		if($params['PrefCd'][0] == '01'){
			$data['chizuGazo']["M85_TODOUFUKEN_NAME"] = "北海道";
		}else{
			foreach ($data['sikucyoson'] as $listData)
			{
				// 地図画像用データ
				$data['Mapping']['MAP'][] = array(
				'SHAPE'					=> $listData['M84_SHAPE'],
				'TODOUFUKEN_CD'			=> $listData['M84_TODOUFUKEN_CD'],
				'SHIKUCHOUSON'			=> $listData['M84_SHIKUCHOUSON_NAME'],
				'SHIKUCHOUSON_CD'		=> $listData['M84_SHIKUCHOUSON_CD'],
				'IDO'					=> number_format($listData['M84_IDO']/3600,6),
				'KEIDO'					=> number_format($listData['M84_KEIDO']/3600,6),
				'COORDS'				=> $listData['M84_COORDS']
				);

				// 同一市区町村名判定
				if($tempName != $listData['M84_SHIKUCHOUSON_NAME'])
				{
					// 市区町村選択データ
					$data['Mapping']['AREA_DATA'][] = array(
					'TODOUFUKEN_CD'			=> $listData['M84_TODOUFUKEN_CD'],
					'SHIKUCHOUSON'			=> $listData['M84_SHIKUCHOUSON_NAME'],
					'SHIKUCHOUSON_CD'		=> $listData['M84_SHIKUCHOUSON_CD'],
					'IDO'					=> number_format($listData['M84_IDO']/3600,6),
					'KEIDO'					=> number_format($listData['M84_KEIDO']/3600,6),
					);
				}

				$tempName = $listData['M84_SHIKUCHOUSON_NAME'];
			}

//			foreach ($data['sikucyoson'] as $listData)
//			{
//				$kashiramoji = $listData['M01_SORT1'];
//
//				// 目次が異なる場合
//				if (array_key_exists($kashiramoji, $data['Mapping']) == false)
//				{
//					$data['Mapping']["$kashiramoji"]['MOKUJI'] = $listData['M01_SORT1'];
//					$data['Mapping']["$kashiramoji"][] = array();
//
//				}
//
//				$data['Mapping']["$kashiramoji"]['SHIKUCHOUSON_DATA'][] = array(
//				'SHAPE'					=> $listData['M84_SHAPE'],
//				'TODOUFUKEN_CD'			=> $listData['M84_TODOUFUKEN_CD'],
//				'SHIKUCHOUSON'			=> $listData['M84_SHIKUCHOUSON_NAME'],
//				'SHIKUCHOUSON_CD'		=> $listData['M84_SHIKUCHOUSON_CD'],
//				'IDO'					=> number_format($listData['M84_IDO']/3600,6),
//				'KEIDO'					=> number_format($listData['M84_KEIDO']/3600,6),
//				'COORDS'				=> $listData['M84_COORDS']
//
//				);
//			}
		}
	}
    /**
     * 表示地図画像取得SQL文の取得
     *
     * 表示地図画像取得SQL文の取得を行う
     * @access	public
     * @param	array
     * @param	array
     * @return	string 表示地図画像取得SQL
     */
    public function generateChizuGazoSql($todohuken)
    {
		$sql = "";
		$sql .= "SELECT";
		$sql .= "        REPLACE(M85_SRC,'images/','') M85_SRC";
		$sql .= "        ,M85_TODOUFUKEN_NAME";
		$sql .= "    FROM";
		$sql .= "        M85LOCATION_TODOUFUKEN";
		$sql .= "    WHERE";
		$sql .= "        M85_TODOUFUKEN_CD = '".$todohuken."'";

        return $sql;
    }

	/**
	* 市区町村マップ値取得SQL文の取得
	*
	* 市区町村マップ値取得SQL文の取得を行う
	* @access	public
	* @param	array
	* @param	string 都道府県CD
	* @return	string 表示地図画像取得SQL
	*/
    public function generateSikucyosonSql($mTodouhuken,$tTodohuken)
    {
		$sql = "";
		$sql .= "SELECT";
		$sql .= "        DISTINCT M84_SHAPE";
		$sql .= "        ,M84_COORDS";
		$sql .= "        ,'".$tTodohuken."' AS M84_TODOUFUKEN_CD";
		$sql .= "        ,M84_SHIKUCHOUSON_CD";
		$sql .= "        ,M84_SHIKUCHOUSON_NAME";
		$sql .= "        ,M84_IDO";
		$sql .= "        ,M84_KEIDO";
		$sql .= "        ,(";
		$sql .= "            CASE";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ｱ%' THEN 'あ'";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ｲ%' THEN 'あ'";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ｳ%' THEN 'あ'";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ｴ%' THEN 'あ'";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ｵ%' THEN 'あ'";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ｶ%' THEN 'か'";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ｷ%' THEN 'か'";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ｸ%' THEN 'か'";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ｹ%' THEN 'か'";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ｺ%' THEN 'か'";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ｻ%' THEN 'さ'";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ｼ%' THEN 'さ'";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ｽ%' THEN 'さ'";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ｾ%' THEN 'さ'";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ｿ%' THEN 'さ'";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ﾀ%' THEN 'た'";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ﾁ%' THEN 'た'";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ﾂ%' THEN 'た'";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ﾃ%' THEN 'た'";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ﾄ%' THEN 'た'";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ﾅ%' THEN 'な'";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ﾆ%' THEN 'な'";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ﾇ%' THEN 'な'";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ﾈ%' THEN 'な'";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ﾉ%' THEN 'な'";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ﾊ%' THEN 'は'";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ﾋ%' THEN 'は'";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ﾌ%' THEN 'は'";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ﾍ%' THEN 'は'";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ﾎ%' THEN 'は'";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ﾏ%' THEN 'ま'";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ﾐ%' THEN 'ま'";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ﾑ%' THEN 'ま'";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ﾒ%' THEN 'ま'";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ﾓ%' THEN 'ま'";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ﾔ%' THEN 'や'";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ﾕ%' THEN 'や'";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ﾖ%' THEN 'や'";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ﾗ%' THEN 'ら'";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ﾘ%' THEN 'ら'";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ﾙ%' THEN 'ら'";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ﾚ%' THEN 'ら'";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ﾛ%' THEN 'ら'";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ﾜ%' THEN 'わ'";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ｦ%' THEN 'わ'";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ﾝ%' THEN 'わ'";
		$sql .= "            END";
		$sql .= "        ) AS M01_SORT1";
		$sql .= "        ,(";
		$sql .= "            CASE";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ｱ%' THEN '1'";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ｲ%' THEN '2'";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ｳ%' THEN '3'";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ｴ%' THEN '4'";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ｵ%' THEN '5'";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ｶ%' THEN '6'";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ｷ%' THEN '7'";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ｸ%' THEN '8'";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ｹ%' THEN '9'";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ｺ%' THEN '10'";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ｻ%' THEN '11'";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ｼ%' THEN '12'";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ｽ%' THEN '13'";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ｾ%' THEN '14'";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ｿ%' THEN '15'";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ﾀ%' THEN '16'";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ﾁ%' THEN '17'";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ﾂ%' THEN '18'";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ﾃ%' THEN '19'";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ﾄ%' THEN '20'";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ﾅ%' THEN '21'";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ﾆ%' THEN '22'";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ﾇ%' THEN '23'";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ﾈ%' THEN '24'";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ﾉ%' THEN '25'";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ﾊ%' THEN '26'";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ﾋ%' THEN '27'";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ﾌ%' THEN '28'";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ﾍ%' THEN '29'";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ﾎ%' THEN '30'";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ﾏ%' THEN '31'";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ﾐ%' THEN '32'";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ﾑ%' THEN '33'";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ﾒ%' THEN '34'";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ﾓ%' THEN '35'";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ﾔ%' THEN '36'";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ﾕ%' THEN '37'";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ﾖ%' THEN '38'";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ﾗ%' THEN '39'";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ﾘ%' THEN '40'";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ﾙ%' THEN '41'";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ﾚ%' THEN '42'";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ﾛ%' THEN '43'";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ﾜ%' THEN '44'";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ｦ%' THEN '45'";
		$sql .= "                WHEN T93_SHIKUCHOUSON_KANA LIKE 'ﾝ%' THEN '46'";
		$sql .= "            END";
		$sql .= "        ) AS M01_SORT2";
		$sql .= "    FROM";
		$sql .= "        M84LOCATION_COORD";
		$sql .= "        ,T93JYUUSHO_SHIKUCHOUSON_SUM";
		$sql .= "    WHERE";
		$sql .= "        M84_TODOUFUKEN_CD = '".$mTodouhuken."'";
		$sql .= "        AND T93_TODOUFUKEN_CD = '".$tTodohuken."'";
		$sql .= "        AND T93_SHIKUCHOUSON_CD = M84_SHIKUCHOUSON_CD";
		$sql .= "        AND M84_IDO IS NOT NULL";
		$sql .= "    ORDER BY";
		$sql .= "        M01_SORT1";
		$sql .= "        ,M01_SORT2";
		$sql .= "        ,M84_SHIKUCHOUSON_CD";

        return $sql;
    }

	/**
	* 都道府県地図データ取得SQL
	*
	* 都道府県地図データ取得SQL文の取得を行う
	* @access	public
	* @param	array
	* @param	string 都道府県CD
	* @return	string 都道府県地図データ
	*/
	public function generateTodofukenChizuSql($todouhuken,$option)
	{
		$sql = "";
	    $sql .= "SELECT";
		$sql .= "        DISTINCT M84_TODOUFUKEN_CD";
		$sql .= "        ,M84_SHAPE";
		$sql .= "        ,M84_COORDS";
		$sql .= "        ,M84_SHIKUCHOUSON_CD";
		$sql .= "        ,M84_SHIKUCHOUSON_NAME";
		$sql .= "    FROM";
		$sql .= "        M84LOCATION_COORD";
		$sql .= "    WHERE";
		$sql .= "        M84_TODOUFUKEN_CD = '".$todouhuken."'";
		$sql .= $option;

		return $sql;
	}

	/**
	* 北海道地域データ取得SQL
	*
	* 北海道地域データ取得SQL文の取得を行う
	* @access	public
	* @param	array
	* @param	string 都道府県CD
	* @return	string 表示地図画像取得SQL
	*/
	public function generateHokkaidoSql($params)
	{
		$sql = "";
		$sql .= "SELECT";
		$sql .= "        DISTINCT M85_TODOUFUKEN_CD";
		$sql .= "        ,M85_TODOUFUKEN_NAME";
		$sql .= "    FROM";
		$sql .= "        M85LOCATION_TODOUFUKEN";
		$sql .= "    WHERE";
		$sql .= "        M85_TODOUFUKEN_CD = '50'";
		$sql .= "        OR M85_TODOUFUKEN_CD = '51'";
		$sql .= "        OR M85_TODOUFUKEN_CD = '52'";
		$sql .= "        OR M85_TODOUFUKEN_CD = '53'";
		$sql .= "    ORDER BY";
		$sql .= "        M85_TODOUFUKEN_CD";

		return $sql;
    }

	/**
	 *  緯度・経度から市区町村を取得
	 */
	public function getCityCdFromLocation($params)
	{
		$db_params = array();
		$sql = $this->genetateCityCdFromLocation($params, $db_params);
		return $this->dbGetData($sql, $db_params);
	}

	private function genetateCityCdFromLocation($params, &$db_params)
	{
		$db_params['x'] = $params['x'];
		$db_params['y'] = $params['y'];
		$sql  = "";
		$sql .= "SELECT ";
		$sql .= "     M01_TODOUFUKEN_CD as PREF_CD ";
		$sql .= "    ,M01_TODOUFUKEN_CD || M01_SHIKUCHOUSON_CD as CITY_CD ";
		$sql .= "  FROM ";
		$sql .= "     M01JYUUSHO_LOCATION ";
		$sql .= " WHERE ";
		$sql .= "     M01_JYUUSHO_CD = (";
		$sql .= "         SELECT M01_JYUUSHO_CD ";
		$sql .= "           FROM M01JYUUSHO_LOCATION ";
		$sql .= "          WHERE ";
		$sql .= "         ";
		$sql .= "             MDSYS.SDO_NN (M01_LOCATION, ";
		$sql .= "                 MDSYS.SDO_GEOMETRY( 2001, 8301, MDSYS.sdo_point_type( ";
		$sql .= "                   :x ";
		$sql .= "                 , :y ";
		$sql .= "                 , NULL ) ,NULL ,NULL) ";
		$sql .= "             , 'querytype=WINDOW') = 'TRUE'  AND ROWNUM = 1 ";
		$sql .= "     ) ";
		return $sql;
	}

}
?>
